package com.softer.wxzj.controller;

import com.github.pagehelper.PageInfo;
import com.softer.wxzj.common.aop.annotation.LogRecord;
import com.softer.wxzj.common.aop.enums.LogOperationType;
import com.softer.wxzj.common.aop.enums.LogOperationUnit;
import com.softer.wxzj.common.cellStyle.CreateTitleCellStyle;
import com.softer.wxzj.common.entity.Page;
import com.softer.wxzj.common.util.DateUtils;
import com.softer.wxzj.entity.ExpPayReq;
import com.softer.wxzj.entity.RegionBank;
import com.softer.wxzj.entity.StatisticalReportForms;
import com.softer.wxzj.service.StatisticalReportFormsService;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.List;

/**
 * @author Niu
 * @create 2019/11/11 - 15:09 - 星期一
 * 统计分析 模块
 */
@Slf4j
@RequestMapping("/statisticalReportForms")
@RestController
public class StatisticalReportFormsController {

    @Autowired
    private StatisticalReportFormsService statisticalReportFormsService;

    private static final String IO_EXCRPTION="IOException错误信息:()";
    /**
     * 综合查询
     *
     * @param state 状态（0：综合查询；1：综合查询（全））
     * @return
     */
    @RequestMapping("/wholeCityComprehensiveQuery")
    @LogRecord(description = "统计分析/综合查询全（综合查询全）", operationUnit = LogOperationUnit.STATISTICAL_REPORT_FORMS, operationType = LogOperationType.SELECT)
    public List<StatisticalReportForms> wholeCityComprehensiveQuery(String state, String houseAttr, String houseUse) {
        return statisticalReportFormsService.wholeCityComprehensiveQuery(state, houseAttr, houseUse);
    }

    /**
     * 综合查询--小区
     *
     * @param page  页数 默认不给
     * @param state 状态（1：综合查询（全））
     * @return
     */
    @RequestMapping("/wholeCityComprehensiveQueryRegion")
    @LogRecord(description = "统计分析/综合查询全（综合查询全）-小区信息", operationUnit = LogOperationUnit.STATISTICAL_REPORT_FORMS, operationType = LogOperationType.SELECT)
    public PageInfo<StatisticalReportForms> wholeCityComprehensiveQueryRegion(Page page, String state, String fuzzy) {
        return statisticalReportFormsService.wholeCityComprehensiveQueryRegion(page, state, fuzzy);
    }

    /**
     * 查询本年(yue)的年初到年末
     */

    @RequestMapping("/selectStartAndEnd")
    @LogRecord(description = "查询本年的年初到年末", operationUnit = LogOperationUnit.STATISTICAL_REPORT_FORMS, operationType = LogOperationType.SELECT)
    public StatisticalReportForms selectStartAndEnd(String type) {
        return statisticalReportFormsService.selectStartAndEnd(type);
    }

    /**
     * 综合查询--自然幢
     *
     * @param page     页数 默认不给
     * @param regionId
     * @return
     */
    @RequestMapping("/wholeCityComprehensiveQueryBuild")
    @LogRecord(description = "统计分析/综合查询全-自然幢", operationUnit = LogOperationUnit.STATISTICAL_REPORT_FORMS, operationType = LogOperationType.SELECT)
    PageInfo<StatisticalReportForms> wholeCityComprehensiveQueryBuild(Page page, String regionId, String fuzzy) {
        return statisticalReportFormsService.wholeCityComprehensiveQueryBuild(page, regionId, fuzzy);
    }

    /**
     * 综合查询--自然幢
     *
     * @param page    页数 默认不给
     * @param buildId
     * @return
     */
    @RequestMapping("/wholeCityComprehensiveQueryHouse")
    @LogRecord(description = "统计分析/综合查询全-房屋", operationUnit = LogOperationUnit.STATISTICAL_REPORT_FORMS, operationType = LogOperationType.SELECT)
    PageInfo<StatisticalReportForms> wholeCityComprehensiveQueryHouse(Page page, String buildId, String fuzzy) {
        return statisticalReportFormsService.wholeCityComprehensiveQueryHouse(page, buildId, fuzzy);
    }

    /**
     * 维修资金统计报表
     *
     * @param type
     * @return
     */
    @RequestMapping("/wholeMoneyOutAndIn")
    @LogRecord(description = "统计分析/综合查询全-房屋", operationUnit = LogOperationUnit.STATISTICAL_REPORT_FORMS, operationType = LogOperationType.SELECT)
    RegionBank wholeMoneyOutAndIn(String type) {
        return statisticalReportFormsService.wholeMoneyOutAndIn(type);
    }

    /**
     * 小区交存业务查询
     *
     * @param page  分页参数
     * @param fuzzy 模糊查询
     * @return
     */
    @RequestMapping("/depositBusinessQuery")
    @LogRecord(description = "小区交存业务查询", operationUnit = LogOperationUnit.STATISTICAL_REPORT_FORMS, operationType = LogOperationType.SELECT)
    public PageInfo<StatisticalReportForms> depositBusinessQuery(Page page, String fuzzy, String queryModes) {
        return statisticalReportFormsService.depositBusinessQuery(page, fuzzy, queryModes);
    }

    /**
     * 小区交存业务查询==》查看
     *
     * @param page
     * @param fuzzy
     * @return
     */
    @RequestMapping("/depositDetails")
    @LogRecord(description = "小区交存业务查询/查看", operationUnit = LogOperationUnit.STATISTICAL_REPORT_FORMS, operationType = LogOperationType.SELECT)
    public PageInfo<StatisticalReportForms> depositDetails(Page page, String fuzzy, String no, String buildId) {
        return statisticalReportFormsService.depositDetails(page, fuzzy, no, buildId);
    }

    /**
     * 小区秦欠费信息查询
     *
     * @param page
     * @param fuzzy 模糊查询条件
     * @return
     */
    @RequestMapping("/arrearsInquiries")
    @LogRecord(description = "小区欠费信息查询", operationUnit = LogOperationUnit.STATISTICAL_REPORT_FORMS, operationType = LogOperationType.SELECT)
    public PageInfo<StatisticalReportForms> arrearsInquiries(Page page, String fuzzy) {
        return statisticalReportFormsService.arrearsInquiries(page, fuzzy);
    }

    /**
     * 小区秦欠费信息查询详情
     *
     * @param page
     * @param fuzzy 模糊查询条件
     * @return
     */
    @RequestMapping("/arrearsInquiriesDetails")
    @LogRecord(description = "小区欠费信息查询详情", operationUnit = LogOperationUnit.STATISTICAL_REPORT_FORMS, operationType = LogOperationType.SELECT)
    public PageInfo<StatisticalReportForms> arrearsInquiriesDetails(Page page, String fuzzy, String id) {
        return statisticalReportFormsService.arrearsInquiriesDetails(page, fuzzy, id);
    }

    /**
     * 小区业务汇总查询
     *
     * @param page
     * @param statisticalReportForms
     * @return
     */
    @RequestMapping("/businessSummaryQuery")
    @LogRecord(description = "小区业务汇总查询", operationUnit = LogOperationUnit.STATISTICAL_REPORT_FORMS, operationType = LogOperationType.SELECT)
    public PageInfo<StatisticalReportForms> businessSummaryQuery(Page page, StatisticalReportForms statisticalReportForms) {
        return statisticalReportFormsService.businessSummaryQuery(page, statisticalReportForms);
    }

    /**
     * 小区业务明细查询
     *
     * @param page
     * @param statisticalReportForms
     * @return
     */
    @RequestMapping("/businessDetailsQuery")
    @LogRecord(description = "小区业务明细查询", operationUnit = LogOperationUnit.STATISTICAL_REPORT_FORMS, operationType = LogOperationType.SELECT)
    public PageInfo<StatisticalReportForms> businessDetailsQuery(Page page, StatisticalReportForms statisticalReportForms) {
        return statisticalReportFormsService.businessDetailsQuery(page, statisticalReportForms);
    }

    /**
     * 小区业务明细查询  房间分组列表
     *
     * @param page
     * @param statisticalReportForms
     * @return
     */
    @RequestMapping("/businessDetailsSee")
    @LogRecord(description = "小区业务明细查询--查询（房间列表数据）", operationUnit = LogOperationUnit.STATISTICAL_REPORT_FORMS, operationType = LogOperationType.SELECT)
    public PageInfo<StatisticalReportForms> businessDetailsSee(Page page, StatisticalReportForms statisticalReportForms) {
        return statisticalReportFormsService.businessDetailsSee(page, statisticalReportForms);
    }

    /**
     * 银行交易明细查询
     */
    @RequestMapping("/selectBank")
    @LogRecord(description = "银行交易明细查询", operationUnit = LogOperationUnit.STATISTICAL_REPORT_FORMS, operationType = LogOperationType.SELECT)
    public PageInfo<StatisticalReportForms> selectBank(Page page, StatisticalReportForms statisticalReportForms) {
        return statisticalReportFormsService.selectBank(page, statisticalReportForms);
    }

    /**
     * 银行交易明细查询--明细
     */
    @RequestMapping("/selectBankDefinite")
    @LogRecord(description = "银行交易明细查询明细", operationUnit = LogOperationUnit.STATISTICAL_REPORT_FORMS, operationType = LogOperationType.SELECT)
    public PageInfo<StatisticalReportForms> selectBankDefinite(Page page, StatisticalReportForms s) {
        return statisticalReportFormsService.selectBankDefinite(page, s);
    }

    /**
     * 银行交易汇总查询
     *
     * @param page
     * @param statisticalReportForms
     * @return
     */
    @RequestMapping("/bankTransactionSummaryQuery")
    @LogRecord(description = "银行交易汇总查询", operationUnit = LogOperationUnit.STATISTICAL_REPORT_FORMS, operationType = LogOperationType.SELECT)
    public PageInfo<StatisticalReportForms> bankTransactionSummaryQuery(Page page, StatisticalReportForms statisticalReportForms) {
        return statisticalReportFormsService.bankTransactionSummaryQuery(page, statisticalReportForms);
    }

    /**
     * 银行交易汇总查询
     *
     * @param type
     * @return
     */
    @RequestMapping("/wholebankTransaction")
    @LogRecord(description = "总账汇总", operationUnit = LogOperationUnit.STATISTICAL_REPORT_FORMS, operationType = LogOperationType.SELECT)
    public List<StatisticalReportForms> wholebankTransaction(String type) {
        return statisticalReportFormsService.wholebankTransaction(type);
    }

    /**
     * 银行交易汇总查询
     *
     * @return
     */
    @RequestMapping("/wholebankTransactionCell")
    @LogRecord(description = "总账汇总", operationUnit = LogOperationUnit.STATISTICAL_REPORT_FORMS, operationType = LogOperationType.SELECT)
    public void wholebankTransactionCell(@RequestBody StatisticalReportForms statisticalReportForms, HttpServletResponse response) {
        try {
            List<StatisticalReportForms> companyList = statisticalReportFormsService.wholebankTransaction(statisticalReportForms.getType());
            companyList.get(0).setDate("");
            companyList.get(0).setDay("");
            companyList.get(0).setNO("");
            companyList.get(0).setRegionName("");
            companyList.get(0).setSubRegionName("");
            companyList.get(0).setRemark("");
            companyList.get(0).setPayAmount(new BigDecimal(0));
            companyList.get(0).setRefundAmount(new BigDecimal(0));
            for (StatisticalReportForms s : companyList) {
                if (null == s.getDueAmount()) {
                    s.setDueAmount(new BigDecimal(0));
                }
            }
            companyList.get(companyList.size() - 1).setDate("");
            companyList.get(companyList.size() - 1).setDay("");
            companyList.get(companyList.size() - 1).setNO("");
            companyList.get(companyList.size() - 1).setRegionName("");
            companyList.get(companyList.size() - 1).setSubRegionName("");
            companyList.get(companyList.size() - 1).setRemark("");
            HSSFWorkbook wb = new HSSFWorkbook();
//          工作簿名称
            HSSFSheet sheet = wb.createSheet("总账汇总");
//          合并单元格
//          表头
            sheet.setDefaultColumnWidth(10);
            sheet.setColumnWidth(0, 8 * 256);
            sheet.setColumnWidth(1, 8 * 256);
            sheet.setColumnWidth(2, 20 * 256);
            sheet.setColumnWidth(6, 35 * 256);
            HSSFRow row = null;
            row = sheet.createRow(0);
            //          设置行高
            row.setHeight((short) (30 * 20));
            CellRangeAddress rowRegion01 = new CellRangeAddress(0, 0, 0, 1);
            sheet.addMergedRegion(rowRegion01);
            CellRangeAddress rowRegion04 = new CellRangeAddress(0, 1, 2, 2);
            sheet.addMergedRegion(rowRegion04);
            CellRangeAddress rowRegion05 = new CellRangeAddress(0, 1, 3, 3);
            sheet.addMergedRegion(rowRegion05);
            CellRangeAddress rowRegion06 = new CellRangeAddress(0, 1, 4, 4);
            sheet.addMergedRegion(rowRegion06);
            CellRangeAddress rowRegion08 = new CellRangeAddress(0, 1, 5, 5);
            sheet.addMergedRegion(rowRegion08);
            CellRangeAddress rowRegion09 = new CellRangeAddress(0, 1, 6, 6);
            sheet.addMergedRegion(rowRegion09);
            CellRangeAddress rowRegion10 = new CellRangeAddress(0, 1, 7, 7);
            sheet.addMergedRegion(rowRegion10);
            CellRangeAddress rowRegion11 = new CellRangeAddress(0, 1, 8, 8);
            sheet.addMergedRegion(rowRegion11);
            CellRangeAddress rowRegion12 = new CellRangeAddress(0, 1, 9, 9);
            sheet.addMergedRegion(rowRegion12);
            row.createCell(0).setCellValue(statisticalReportForms.getType().substring(0, 4));
            row.createCell(2).setCellValue("凭证号码");
            row.createCell(3).setCellValue("单位");
            row.createCell(4).setCellValue("小区");
            row.createCell(5).setCellValue("小区分区");
            row.createCell(6).setCellValue("摘要");
            row.createCell(7).setCellValue("收入余额");
            row.createCell(8).setCellValue("支出余额");
            row.createCell(9).setCellValue("专户余额");
            row = sheet.createRow(1);
            //          设置行高
            row.setHeight((short) (30 * 20));
            row.createCell(0).setCellValue("月");
            row.createCell(1).setCellValue("日 ");
            //绑定数据
            for (int i = 0; i < companyList.size(); i++) {
                row = sheet.createRow(i + 2);
                StatisticalReportForms company = companyList.get(i);
                row.createCell(0).setCellValue(company.getDate());
                row.createCell(1).setCellValue(String.valueOf(company.getDay()));
                row.createCell(2).setCellValue(String.valueOf(company.getNO()));
                row.createCell(3).setCellValue(String.valueOf(company.getName()));
                row.createCell(4).setCellValue(String.valueOf(company.getRegionName()));
                row.createCell(5).setCellValue(String.valueOf(company.getSubRegionName()));
                row.createCell(6).setCellValue(String.valueOf(company.getRemark()));
                row.createCell(7).setCellValue(String.valueOf(company.getPayAmount()));
                row.createCell(8).setCellValue(String.valueOf(company.getRefundAmount()));
                row.createCell(9).setCellValue(String.valueOf(company.getDueAmount()));
            }
            String title = DateUtils.getNow() + "";
            String fileName = new String(title.getBytes("GB2312"), "ISO8859-1");
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;fileName=" + fileName + ".xls");
            OutputStream os = response.getOutputStream();
            //默认Excel名称
            wb.write(os);
            os.flush();
            os.close();
        } catch (IOException e) {
            log.error(IO_EXCRPTION, e.getMessage(), e);
        }
    }

    /**
     * 资金缴存统计
     */
    @RequestMapping("/selectdepAll")
    @LogRecord(description = "资金缴存统计", operationUnit = LogOperationUnit.STATISTICAL_REPORT_FORMS, operationType = LogOperationType.SELECT)
    public List<RegionBank> selectdepAll(String startTime, String endTime) {
        return statisticalReportFormsService.selectdepAll(startTime, endTime);
    }

    /**
     * 资金缴存统计  报表
     */
    @RequestMapping("/selectdepAllCell")
    @LogRecord(description = "资金缴存统计", operationUnit = LogOperationUnit.STATISTICAL_REPORT_FORMS, operationType = LogOperationType.SELECT)
    public void selectdepAll(@RequestBody StatisticalReportForms statisticalReportForms, HttpServletResponse response) {
        try {
            List<RegionBank> companyList = statisticalReportFormsService.selectdepAllCell(statisticalReportForms.getStartTime(), statisticalReportForms.getEndTime());
            HSSFWorkbook wb = new HSSFWorkbook();
//          工作簿名称
            HSSFSheet sheet = wb.createSheet("资金缴存统计（银行）");
//          合并单元格
//          表头
            HSSFRow row = null;
            row = sheet.createRow(0);
            //          设置行高
            row.setHeight((short) (30 * 20));
            CellRangeAddress rowRegion01 = new CellRangeAddress(0, 1, 0, 0);
            sheet.addMergedRegion(rowRegion01);
            CellRangeAddress rowRegion02 = new CellRangeAddress(0, 1, 1, 1);
            sheet.addMergedRegion(rowRegion02);
            for (int i = 0; i < companyList.get(0).getBankInfos().size(); i++) {
                CellRangeAddress rowRegion03 = new CellRangeAddress(0, 0, i * 4 + 2, 5 + 4 * i);
                sheet.addMergedRegion(rowRegion03);
            }

            row.createCell(0).setCellValue("单位名称");
            row.createCell(1).setCellValue("小区名称");
            for (int i = 0; i < companyList.get(0).getBankInfos().size(); i++) {
                RegionBank company = companyList.get(i);
                row.createCell(i * 4 + 2).setCellValue(company.getBankInfos().get(i).getBankName());
            }
            row = sheet.createRow(1);

            //          设置行高
            row.setHeight((short) (30 * 20));
            for (int i = 0; i < companyList.get(0).getBankInfos().size(); i++) {
                row.createCell(2 + i * 4).setCellValue("户数");
                row.createCell(3 + i * 4).setCellValue("交存金额（元）");
                row.createCell(4 + i * 4).setCellValue("利息（元）");
                row.createCell(5 + i * 4).setCellValue("管理费（元）");
            }
            //绑定数据
            for (int i = 0; i < companyList.size(); i++) {
                row = sheet.createRow(i + 2);
                RegionBank company = companyList.get(i);
                row.createCell(0).setCellValue(company.getOrgName());
                row.createCell(1).setCellValue(String.valueOf(company.getRegionName()));
                for (int j = 0; j < companyList.get(0).getBankInfos().size(); j++) {
                    row.createCell(2 + j * 4).setCellValue(String.valueOf(company.getBankInfos().get(j).getNum()));
                    row.createCell(3 + j * 4).setCellValue(String.valueOf(company.getBankInfos().get(j).getDueAmount()));
                    row.createCell(4 + j * 4).setCellValue(String.valueOf(company.getBankInfos().get(j).getPayAmount()));
                    row.createCell(5 + j * 4).setCellValue(String.valueOf(0));
                }
            }
            String title = DateUtils.getNow() + "";
            String fileName = new String(title.getBytes("GB2312"), "ISO8859-1");
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;fileName=" + fileName + ".xls");
            OutputStream os = response.getOutputStream();
            //默认Excel名称
            wb.write(os);
            os.flush();
            os.close();
        } catch (IOException e) {
            log.error(IO_EXCRPTION, e.getMessage(), e);
        }
    }

    /**
     * 银行明细入账
     */
    @RequestMapping("/selectbankLinkAll")
    @LogRecord(description = "银行明细入账", operationUnit = LogOperationUnit.STATISTICAL_REPORT_FORMS, operationType = LogOperationType.SELECT)
    public List<RegionBank> selectbankLinkAll(String type) {
        return statisticalReportFormsService.selectbankLinkAll(type);
    }

    /**
     * 银行明细入账
     */
    @RequestMapping("/selectbankLinkAllCell")
    @LogRecord(description = "银行明细入账", operationUnit = LogOperationUnit.STATISTICAL_REPORT_FORMS, operationType = LogOperationType.SELECT)
    public void selectbankLinkAllCell(@RequestBody StatisticalReportForms statisticalReportForms, HttpServletResponse response) {
        try {
            List<RegionBank> companyList = statisticalReportFormsService.selectbankLinkAll(statisticalReportForms.getType());
            companyList.get(0).setDay("");
            companyList.get(0).setDate("");
            HSSFWorkbook wb = new HSSFWorkbook();
//          工作簿名称
            HSSFSheet sheet = wb.createSheet("银行明细入账");
//          合并单元格
//          表头
            HSSFRow row = null;
            row = sheet.createRow(0);
            sheet.setDefaultColumnWidth(10);
            sheet.setColumnWidth(2, 25 * 256);
            //          设置行高
            row.setHeight((short) (30 * 20));
            CellRangeAddress rowRegion01 = new CellRangeAddress(0, 0, 0, 1);
            sheet.addMergedRegion(rowRegion01);
            CellRangeAddress rowRegion04 = new CellRangeAddress(0, 1, 2, 2);
            sheet.addMergedRegion(rowRegion04);
            CellRangeAddress rowRegion05 = new CellRangeAddress(0, 1, 3, 3);
            sheet.addMergedRegion(rowRegion05);
            CellRangeAddress rowRegion06 = new CellRangeAddress(0, 1, 4, 4);
            sheet.addMergedRegion(rowRegion06);
            CellRangeAddress rowRegion07 = new CellRangeAddress(0, 1, 5, 5);
            sheet.addMergedRegion(rowRegion07);
            for (int i = 0; i < companyList.get(0).getBankInfos().size(); i++) {
                CellRangeAddress rowRegion08 = new CellRangeAddress(0, 1, i + 6, i + 6);
                sheet.addMergedRegion(rowRegion08);
            }
            row.createCell(0).setCellValue(statisticalReportForms.getType().substring(0, 4));
            row.createCell(2).setCellValue("单位（开发公司）/小区/幢号");
            row.createCell(3).setCellValue("支出（借方）");
            row.createCell(4).setCellValue("收入（贷方）");
            row.createCell(5).setCellValue("余额");
            for (int i = 0; i < companyList.get(0).getBankInfos().size(); i++) {
                row.createCell(i + 6).setCellValue(companyList.get(0).getBankInfos().get(i).getBankName());
            }
            row = sheet.createRow(1);
            //          设置行高
            row.setHeight((short) (30 * 20));
            row.createCell(0).setCellValue("月");
            row.createCell(1).setCellValue("日");
            for (int i = 0; i < companyList.get(0).getBankInfos().size(); i++) {
                row.createCell(i + 6).setCellValue(companyList.get(0).getBankInfos().get(i).getBankName());
            }
            //绑定数据
            for (int i = 0; i < companyList.size(); i++) {
                row = sheet.createRow(i + 2);
                RegionBank company = companyList.get(i);
                row.createCell(0).setCellValue(company.getDate());
                row.createCell(1).setCellValue(String.valueOf(company.getDay()));
                row.createCell(2).setCellValue(String.valueOf(company.getRegionName()));
                row.createCell(3).setCellValue(String.valueOf(company.getMoney()));
                row.createCell(4).setCellValue(String.valueOf(company.getDueAmount()));
                row.createCell(5).setCellValue(String.valueOf(company.getPayAmount()));
                for (int j = 0; j < companyList.get(0).getBankInfos().size(); j++) {
                    row.createCell(j + 6).setCellValue(String.valueOf(company.getBankInfos().get(j).getFunBal()));
                }
            }
            String title = DateUtils.getNow() + "";
            String fileName = new String(title.getBytes("GB2312"), "ISO8859-1");
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;fileName=" + fileName + ".xls");
            OutputStream os = response.getOutputStream();
            //默认Excel名称
            wb.write(os);
            os.flush();
            os.close();
        } catch (IOException e) {
            log.error(IO_EXCRPTION, e.getMessage(), e);
        }
    }

    /**
     * 使用统计
     */
    @RequestMapping("/selectExpAll")
    @LogRecord(description = "使用统计", operationUnit = LogOperationUnit.STATISTICAL_REPORT_FORMS, operationType = LogOperationType.SELECT)
    public List<ExpPayReq> selectexpAll(String startTime, String endTime) {
        return statisticalReportFormsService.selectexpAll(startTime, endTime);
    }

    /**
     * 使用统计
     */
    @RequestMapping("/selectExpAllCell")
    @LogRecord(description = "使用统计", operationUnit = LogOperationUnit.STATISTICAL_REPORT_FORMS, operationType = LogOperationType.SELECT)
    public void selectexpAllCell(@RequestBody StatisticalReportForms statisticalReportForms, HttpServletResponse response) {
        try {
            List<ExpPayReq> companyList = statisticalReportFormsService.selectexpAll(statisticalReportForms.getStartTime(), statisticalReportForms.getEndTime());
            companyList.get(companyList.size() - 1).setCreateTime("合计");
            companyList.get(companyList.size() - 1).setOrgName("");
            companyList.get(companyList.size() - 1).setReqName("");
            HSSFWorkbook wb = new HSSFWorkbook();
//          工作簿名称
            HSSFSheet sheet = wb.createSheet("资金支用统计");
//          合并单元格
//          表头
            HSSFRow row = null;
            row = sheet.createRow(0);
            //          设置行高
            row.setHeight((short) (30 * 20));
            row.createCell(0).setCellValue("日期");
            row.createCell(1).setCellValue("申请单位");
            row.createCell(2).setCellValue("项目名称");
            row.createCell(3).setCellValue("自交金额（元）");
            row.createCell(4).setCellValue("系统支付金额 （元）");
            row.createCell(5).setCellValue("总金额额（元）");
            //绑定数据
            for (int i = 0; i < companyList.size(); i++) {
                row = sheet.createRow(i + 1);
                ExpPayReq company = companyList.get(i);
                row.createCell(0).setCellValue(company.getCreateTime());
                row.createCell(1).setCellValue(String.valueOf(company.getOrgName()));
                row.createCell(2).setCellValue(String.valueOf(company.getReqName()));
                row.createCell(3).setCellValue(String.valueOf(company.getSelfAmount()));
                row.createCell(4).setCellValue(String.valueOf(company.getSystemPayment()));
                row.createCell(5).setCellValue(String.valueOf(company.getPayAmount()));
            }
            String title = DateUtils.getNow() + "";
            String fileName = new String(title.getBytes("GB2312"), "ISO8859-1");
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;fileName=" + fileName + ".xls");
            OutputStream os = response.getOutputStream();
            //默认Excel名称
            wb.write(os);
            os.flush();
            os.close();
        } catch (IOException e) {
            log.error(IO_EXCRPTION, e.getMessage(), e);
        }
    }

    /**
     * 年度
     *
     * @return
     */
    @RequestMapping("/selectAllYear")
    @LogRecord(description = "年度", operationUnit = LogOperationUnit.STATISTICAL_REPORT_FORMS, operationType = LogOperationType.SELECT)
    public List<Integer> selectAllYear() {
        return statisticalReportFormsService.selectAllYear();
    }

    /**
     * 导出excel
     * exportExcelType：businessDetailsQuery ==》小区业务明细查询
     * exportExcelType：businessSummaryQuery ==》小区业务汇总查询
     *
     * @param statisticalReportForms 参数的实体
     * @param response
     */
    @RequestMapping(value = "/businessDetailsSeeExport", method = RequestMethod.POST)
    @ResponseBody
    public void businessDetailsSeeExport(@RequestBody StatisticalReportForms statisticalReportForms, HttpServletResponse response) {
        try {
            List<StatisticalReportForms> companyList = statisticalReportFormsService.businessDetailsSeeCell(statisticalReportForms);
            HSSFWorkbook wb = new HSSFWorkbook();
//          工作簿名称
            HSSFSheet sheet = wb.createSheet("小区业务明细查询");
//          合并单元格
//          表头
            HSSFRow row = null;
            row = sheet.createRow(0);
            sheet.setColumnWidth(0, 30 * 256);
            //          设置行高
            row.setHeight((short) (30 * 20));
            CellRangeAddress rowRegion01 = new CellRangeAddress(0, 1, 0, 0);
            sheet.addMergedRegion(rowRegion01);
            CellRangeAddress rowRegion02 = new CellRangeAddress(0, 1, 1, 1);
            sheet.addMergedRegion(rowRegion02);
            CellRangeAddress rowRegion03 = new CellRangeAddress(0, 1, 2, 2);
            sheet.addMergedRegion(rowRegion03);
            CellRangeAddress rowRegion04 = new CellRangeAddress(0, 0, 3, 5);
            sheet.addMergedRegion(rowRegion04);
            CellRangeAddress rowRegion05 = new CellRangeAddress(0, 0, 6, 7);
            sheet.addMergedRegion(rowRegion05);
            CellRangeAddress rowRegion06 = new CellRangeAddress(0, 1, 8, 8);
            sheet.addMergedRegion(rowRegion06);
            row.createCell(0).setCellValue("房屋地址");
            row.createCell(1).setCellValue("业主姓名");
            row.createCell(2).setCellValue("期初余额");
            row.createCell(3).setCellValue("本期收入");
            row.createCell(6).setCellValue("本期支出");
            row.createCell(8).setCellValue("期末余额");
            row = sheet.createRow(1);
            //          设置行高
            row.setHeight((short) (30 * 20));
            row.createCell(3).setCellValue("缴存笔数");
            row.createCell(4).setCellValue("利息");
            row.createCell(5).setCellValue("收益");
            row.createCell(6).setCellValue("退款金额");
            row.createCell(7).setCellValue("使用金额");

            //绑定数据
            for (int i = 0; i < companyList.size(); i++) {
                row = sheet.createRow(i + 2);
                StatisticalReportForms company = companyList.get(i);
                row.createCell(0).setCellValue(company.getHouseSite());
                row.createCell(1).setCellValue(String.valueOf(company.getOwnerName()));
                row.createCell(2).setCellValue(String.valueOf(company.getCurrentAmount()));
                row.createCell(3).setCellValue(String.valueOf(company.getPayAmount()));
                row.createCell(4).setCellValue(String.valueOf(company.getInterestAmount()));
                row.createCell(5).setCellValue(String.valueOf(company.getAddedIncomeQuota()));
                row.createCell(6).setCellValue(String.valueOf(company.getRefundAmount()));
                row.createCell(7).setCellValue(String.valueOf(company.getActualAmount()));
                row.createCell(8).setCellValue(String.valueOf(company.getInitialPayAmount()));
            }
            String title = DateUtils.getNow() + "";
            String fileName = new String(title.getBytes("GB2312"), "ISO8859-1");
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;fileName=" + fileName + ".xls");
            OutputStream os = response.getOutputStream();
            //默认Excel名称
            wb.write(os);
            os.flush();
            os.close();
        } catch (IOException e) {
            log.error(IO_EXCRPTION, e.getMessage(), e);
        }
    }

    /**
     * 导出excel
     * exportExcelType：businessDetailsQuery ==》小区业务明细查询
     * exportExcelType：businessSummaryQuery ==》小区业务汇总查询
     *
     * @param statisticalReportForms 参数的实体
     * @param response
     */
    @RequestMapping(value = "/businessQueryExport", method = RequestMethod.POST)
    @ResponseBody
    public void businessQueryExport(@RequestBody StatisticalReportForms statisticalReportForms, HttpServletResponse response) {
        try {
            List<StatisticalReportForms> companyList = statisticalReportFormsService.businessQueryExport(statisticalReportForms);
            HSSFWorkbook wb = new HSSFWorkbook();
//          工作簿名称
            HSSFSheet sheet = wb.createSheet("小区业务查询");
//          合并单元格
//          表头
            HSSFRow row = null;
            row = sheet.createRow(0);
//设置某一行列宽的值
            sheet.setColumnWidth(0, 10 * 256);
            //          设置行高
            row.setHeight((short) (30 * 20));
            if ("businessDetailsQuery".equals(statisticalReportForms.getExportExcelType())) {
                CellRangeAddress rowRegion01 = new CellRangeAddress(0, 1, 0, 0);
                sheet.addMergedRegion(rowRegion01);
                CellRangeAddress rowRegion02 = new CellRangeAddress(0, 1, 1, 1);
                sheet.addMergedRegion(rowRegion02);
                CellRangeAddress rowRegion03 = new CellRangeAddress(0, 0, 2, 4);
                sheet.addMergedRegion(rowRegion03);
                CellRangeAddress rowRegion04 = new CellRangeAddress(0, 0, 5, 6);
                sheet.addMergedRegion(rowRegion04);
                CellRangeAddress rowRegion05 = new CellRangeAddress(0, 1, 7, 7);
                sheet.addMergedRegion(rowRegion05);
                row.createCell(0).setCellValue("自然幢名称");
                row.createCell(1).setCellValue("期初余额");
                row.createCell(2).setCellValue("本期收入");
                row.createCell(5).setCellValue("本期支出");
                row.createCell(7).setCellValue("期末余额");
                row = sheet.createRow(1);
                //          设置行高
                row.setHeight((short) (30 * 20));
                row.createCell(2).setCellValue("交存金额");
                row.createCell(3).setCellValue("利息");
                row.createCell(4).setCellValue("收益");
                row.createCell(5).setCellValue("退款金额");
                row.createCell(6).setCellValue("使用金额");
                row.createCell(7).setCellValue("期末余额");
            } else {
                CellRangeAddress rowRegion01 = new CellRangeAddress(0, 1, 0, 0);
                sheet.addMergedRegion(rowRegion01);
                CellRangeAddress rowRegion02 = new CellRangeAddress(0, 1, 1, 1);
                sheet.addMergedRegion(rowRegion02);
                CellRangeAddress rowRegion03 = new CellRangeAddress(0, 1, 2, 2);
                sheet.addMergedRegion(rowRegion03);
                CellRangeAddress rowRegion04 = new CellRangeAddress(0, 0, 3, 5);
                sheet.addMergedRegion(rowRegion04);
                CellRangeAddress rowRegion05 = new CellRangeAddress(0, 0, 6, 7);
                sheet.addMergedRegion(rowRegion05);
                CellRangeAddress rowRegion06 = new CellRangeAddress(0, 1, 8, 8);
                sheet.addMergedRegion(rowRegion06);
                row.createCell(0).setCellValue("小区编号");
                row.createCell(1).setCellValue("小区名称");
                row.createCell(2).setCellValue("期初余额");
                row.createCell(3).setCellValue("本期收入");
                row.createCell(6).setCellValue("本期支出");
                row.createCell(8).setCellValue("期末余额");
                row = sheet.createRow(1);
                //          设置行高
                row.setHeight((short) (30 * 20));
                row.createCell(3).setCellValue("交存金额");
                row.createCell(4).setCellValue("利息");
                row.createCell(5).setCellValue("收益");
                row.createCell(6).setCellValue("退款金额");
                row.createCell(7).setCellValue("使用金额");
            }

            //绑定数据
            for (int i = 0; i < companyList.size(); i++) {
                row = sheet.createRow(i + 2);
                StatisticalReportForms company = companyList.get(i);
                if ("businessDetailsQuery".equals(statisticalReportForms.getExportExcelType())) {
                    row.createCell(0).setCellValue(company.getBuildName());
                    row.createCell(1).setCellValue(String.valueOf(company.getCurrentAmount()));
                    row.createCell(2).setCellValue(String.valueOf(company.getPayAmount()));
                    row.createCell(3).setCellValue(String.valueOf(company.getInterestAmount()));
                    row.createCell(4).setCellValue(String.valueOf(company.getAddedIncomeQuota()));
                    row.createCell(5).setCellValue(String.valueOf(company.getRefundAmount()));
                    row.createCell(6).setCellValue(String.valueOf(company.getActualAmount()));
                    row.createCell(7).setCellValue(String.valueOf(company.getInitialPayAmount()));

                } else {
                    row.createCell(0).setCellValue(company.getRegionNo());
                    row.createCell(1).setCellValue(company.getRegionName());
                    row.createCell(2).setCellValue(String.valueOf(company.getCurrentAmount()));
                    row.createCell(3).setCellValue(String.valueOf(company.getPayAmount()));
                    row.createCell(4).setCellValue(String.valueOf(company.getInterestAmount()));
                    row.createCell(5).setCellValue(String.valueOf(company.getAddedIncomeQuota()));
                    row.createCell(6).setCellValue(String.valueOf(company.getRefundAmount()));
                    row.createCell(7).setCellValue(String.valueOf(company.getActualAmount()));
                    row.createCell(8).setCellValue(String.valueOf(company.getInitialPayAmount()));

                }
            }

            String title = DateUtils.getNow() + "";
            String fileName = new String(title.getBytes("GB2312"), "ISO8859-1");
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;fileName=" + fileName + ".xls");
            OutputStream os = response.getOutputStream();
            //默认Excel名称
            wb.write(os);
            os.flush();
            os.close();
        } catch (IOException e) {
            log.error(IO_EXCRPTION, e.getMessage(), e);
        }
    }

    /**
     * 综合查询导出excel
     *
     * @param statisticalReportForms 参数的实体
     * @param response
     */
    @RequestMapping(value = "/wholeCityComprehensiveQueryCell", method = RequestMethod.POST)
    @ResponseBody
    public void comprehensiveQueryExport(@RequestBody StatisticalReportForms statisticalReportForms, HttpServletResponse response) {
        try {
            List<StatisticalReportForms> companyList = statisticalReportFormsService.wholeCityComprehensiveQuery(statisticalReportForms.getState(), statisticalReportForms.getHouseAttr(), statisticalReportForms.getHouseUse());
            HSSFWorkbook wb = new HSSFWorkbook();

//          工作簿名称
            HSSFSheet sheet = wb.createSheet("综合查询（全）");
            //设置列宽的值
            sheet.setDefaultColumnWidth(9);
//设置某一行列宽的值
            sheet.setColumnWidth(12, 14 * 256);
            sheet.setColumnWidth(9, 14 * 256);
            sheet.setColumnWidth(13, 14 * 256);
//          合并单元格
//            CellRangeAddress rowRegion01 = new CellRangeAddress(0, 1, 0, 0);
//            sheet.addMergedRegion(rowRegion01);
//          表头
            int rowNum = 0;
            HSSFRow row = sheet.createRow(rowNum++);
            /** 第三步，设置样式以及字体样式*/
            HSSFCellStyle titleStyle = CreateTitleCellStyle.createTitleCellStyle(wb);
            HSSFCell c00 = row.createCell(0);
            String title = "综合查询（全）";
            c00.setCellValue(title);
            c00.setCellStyle(titleStyle);
            row = sheet.createRow(0);
            //          设置行高;
            row.setHeight((short) (20 * 20));
            row.createCell(0).setCellValue("小区数量");
            row.createCell(1).setCellValue("楼栋数量");
            row.createCell(2).setCellValue("房屋数量");
            row.createCell(3).setCellValue("足额户数");
            row.createCell(4).setCellValue("未足额户数");
            row.createCell(5).setCellValue("未交户数");
            row.createCell(6).setCellValue("缴存额");
            row.createCell(7).setCellValue("利息额");
            row.createCell(8).setCellValue("增值收益分配额");
            row.createCell(9).setCellValue("小区收入分配额");
            row.createCell(10).setCellValue("支用笔数");
            row.createCell(11).setCellValue("支用额");
            row.createCell(12).setCellValue("扣除质保金金额");
            row.createCell(13).setCellValue("质保金返回额");
            row.createCell(14).setCellValue("退款笔数");
            row.createCell(15).setCellValue("退款额");
            row.createCell(16).setCellValue("账户余额");
            //绑定数据
            for (int i = 0; i < companyList.size(); i++) {
                row = sheet.createRow(i + 1);
                StatisticalReportForms company = companyList.get(i);
                row.createCell(0).setCellValue(company.getRegionNumber());
                row.createCell(1).setCellValue(String.valueOf(company.getBuildNumber()));
                row.createCell(2).setCellValue(String.valueOf(company.getHouseNumber()));
                row.createCell(3).setCellValue(company.getFullHouseholds());
                row.createCell(4).setCellValue(String.valueOf(company.getInsufficientHouseholds()));
                row.createCell(5).setCellValue(String.valueOf(company.getUnpaidHouseholds()));
                row.createCell(6).setCellValue(String.valueOf(company.getDueAmount()));
                row.createCell(7).setCellValue(String.valueOf(company.getInterestAmount()));
                row.createCell(8).setCellValue(String.valueOf(company.getAddedIncomeQuota()));
                row.createCell(9).setCellValue(String.valueOf(company.getRegionIncomeDistribution()));
                row.createCell(10).setCellValue(String.valueOf(company.getProjectSum()));
                row.createCell(11).setCellValue(String.valueOf(company.getActualAmount()));
                row.createCell(12).setCellValue(String.valueOf(company.getPremium()));
                row.createCell(13).setCellValue(String.valueOf(company.getPremiumReturn()));
                row.createCell(14).setCellValue(String.valueOf(company.getRefundNumber()));
                row.createCell(15).setCellValue(String.valueOf(company.getRefundAmount()));
                row.createCell(16).setCellValue(String.valueOf(company.getFunBal()));

            }

            String titles = DateUtils.getNow() + "";
            String fileName = new String(titles.getBytes("GB2312"), "ISO8859-1");
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;fileName=" + fileName + ".xls");
            OutputStream os = response.getOutputStream();
            //默认Excel名称
            wb.write(os);
            os.flush();
            os.close();
        } catch (IOException e) {
            log.error(IO_EXCRPTION, e.getMessage(), e);
        }
    }

    /**
     * 综合查询导出excel 小区
     *
     * @param response
     */
    @RequestMapping(value = "/wholeCityComprehensiveQueryRegionCell", method = RequestMethod.POST)
    @ResponseBody
    public void wholeCityComprehensiveQueryRegionCell(@RequestBody StatisticalReportForms statisticalReportForms, HttpServletResponse response) {
        try {
            List<StatisticalReportForms> companyList = statisticalReportFormsService.wholeCityComprehensiveQueryRegionCell(statisticalReportForms);
            HSSFWorkbook wb = new HSSFWorkbook();
//          工作簿名称
            HSSFSheet sheet = wb.createSheet("综合查询（全）");

            sheet.setDefaultColumnWidth(9);
//设置某一行列宽的值
            sheet.setColumnWidth(0, 12 * 256);
            sheet.setColumnWidth(12, 14 * 256);
            sheet.setColumnWidth(9, 14 * 256);
            sheet.setColumnWidth(8, 14 * 256);
            sheet.setColumnWidth(13, 14 * 256);
//          合并单元格
//            CellRangeAddress rowRegion01 = new CellRangeAddress(0, 1, 0, 0);
//            sheet.addMergedRegion(rowRegion01);
//          表头
            int rowNum = 0;
            HSSFRow row = sheet.createRow(rowNum++);
            /** 第三步，设置样式以及字体样式*/
            HSSFCellStyle titleStyle = CreateTitleCellStyle.createTitleCellStyle(wb);
            HSSFCell c00 = row.createCell(0);
            String title = "综合查询（全）";
            c00.setCellValue(title);
            c00.setCellStyle(titleStyle);
            row = sheet.createRow(0);
            //          设置行高;
            row.setHeight((short) (20 * 20));
            row.createCell(0).setCellValue("小区名称");
            row.createCell(1).setCellValue("自然幢数量");
            row.createCell(2).setCellValue("房屋数量");
            row.createCell(3).setCellValue("足额户数");
            row.createCell(4).setCellValue("未足额户数");
            row.createCell(5).setCellValue("未交户数");
            row.createCell(6).setCellValue("缴存额");
            row.createCell(7).setCellValue("利息额");
            row.createCell(8).setCellValue("增值收益分配额");
            row.createCell(9).setCellValue("小区收入分配额");
            row.createCell(10).setCellValue("支用笔数");
            row.createCell(11).setCellValue("支用额");
            row.createCell(12).setCellValue("扣除质保金金额");
            row.createCell(13).setCellValue("质保金返回额");
            row.createCell(14).setCellValue("退款笔数");
            row.createCell(15).setCellValue("退款额");
            row.createCell(16).setCellValue("账户余额");
            //绑定数据
            for (int i = 0; i < companyList.size(); i++) {
                row = sheet.createRow(i + 1);
                StatisticalReportForms company = companyList.get(i);
                row.createCell(0).setCellValue(company.getRegionName());
                row.createCell(1).setCellValue(String.valueOf(company.getBuildNumber()));
                row.createCell(2).setCellValue(String.valueOf(company.getHouseNumber()));
                row.createCell(3).setCellValue(company.getFullHouseholds());
                row.createCell(4).setCellValue(String.valueOf(company.getInsufficientHouseholds()));
                row.createCell(5).setCellValue(String.valueOf(company.getUnpaidHouseholds()));
                row.createCell(6).setCellValue(String.valueOf(company.getDueAmount()));
                row.createCell(7).setCellValue(String.valueOf(company.getInterestAmount()));
                row.createCell(8).setCellValue(String.valueOf(company.getAddedIncomeQuota()));
                row.createCell(9).setCellValue(String.valueOf(company.getRegionIncomeDistribution()));
                row.createCell(10).setCellValue(String.valueOf(company.getProjectSum()));
                row.createCell(11).setCellValue(String.valueOf(company.getActualAmount()));
                row.createCell(12).setCellValue(String.valueOf(company.getPremium()));
                row.createCell(13).setCellValue(String.valueOf(company.getPremiumReturn()));
                row.createCell(14).setCellValue(String.valueOf(company.getRefundNumber()));
                row.createCell(15).setCellValue(String.valueOf(company.getRefundAmount()));
                row.createCell(16).setCellValue(String.valueOf(company.getFunBal()));

            }

            String titles = DateUtils.getNow() + "";
            String fileName = new String(titles.getBytes("GB2312"), "ISO8859-1");
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;fileName=" + fileName + ".xls");
            OutputStream os = response.getOutputStream();
            //默认Excel名称
            wb.write(os);
            os.flush();
            os.close();
        } catch (IOException e) {
            log.error(IO_EXCRPTION, e.getMessage(), e);
        }
    }

    /**
     * 综合查询导出excel 自然幢
     *
     * @param response
     */
    @RequestMapping(value = "/wholeCityComprehensiveQueryBuildCell", method = RequestMethod.POST)
    @ResponseBody
    public void wholeCityComprehensiveQueryBuildCell(@RequestBody StatisticalReportForms s, HttpServletResponse response) {
        try {
            List<StatisticalReportForms> companyList = statisticalReportFormsService.wholeCityComprehensiveQueryBuildCell(s);
            HSSFWorkbook wb = new HSSFWorkbook();
//          工作簿名称
            HSSFSheet sheet = wb.createSheet("综合查询（全）");
//          合并单元格
//            CellRangeAddress rowRegion01 = new CellRangeAddress(0, 1, 0, 0);
//            sheet.addMergedRegion(rowRegion01);
//          表头
            int rowNum = 0;
            HSSFRow row = sheet.createRow(rowNum++);
            sheet.setDefaultColumnWidth(9);
//设置某一行列宽的值
            sheet.setColumnWidth(0, 12 * 256);
            sheet.setColumnWidth(8, 14 * 256);
            sheet.setColumnWidth(9, 14 * 256);
            sheet.setColumnWidth(13, 14 * 256);
            sheet.setColumnWidth(12, 14 * 256);

            /** 第三步，设置样式以及字体样式*/
            HSSFCellStyle titleStyle = CreateTitleCellStyle.createTitleCellStyle(wb);
            HSSFCell c00 = row.createCell(0);
            String title = "综合查询（全）";
            c00.setCellValue(title);
            c00.setCellStyle(titleStyle);
            row = sheet.createRow(0);
            //          设置行高;
            row.setHeight((short) (20 * 20));
            row.createCell(0).setCellValue("自然幢名称");
            row.createCell(1).setCellValue("房屋数量");
            row.createCell(2).setCellValue("房屋总面积");
            row.createCell(3).setCellValue("足额户数");
            row.createCell(4).setCellValue("未足额户数");
            row.createCell(5).setCellValue("未交户数");
            row.createCell(6).setCellValue("缴存额");
            row.createCell(7).setCellValue("利息额");
            row.createCell(8).setCellValue("增值收益分配额");
            row.createCell(9).setCellValue("小区收入分配额");
            row.createCell(10).setCellValue("支用笔数");
            row.createCell(11).setCellValue("支用额");
            row.createCell(12).setCellValue("扣除质保金金额");
            row.createCell(13).setCellValue("质保金返回额");
            row.createCell(14).setCellValue("退款笔数");
            row.createCell(15).setCellValue("退款额");
            row.createCell(16).setCellValue("账户余额");
            //绑定数据
            for (int i = 0; i < companyList.size(); i++) {
                row = sheet.createRow(i + 1);
                StatisticalReportForms company = companyList.get(i);
                row.createCell(0).setCellValue(company.getBuildName());
                row.createCell(1).setCellValue(String.valueOf(company.getHouseNumber()));
                row.createCell(2).setCellValue(String.valueOf(company.getStruArea()));
                row.createCell(3).setCellValue(company.getFullHouseholds());
                row.createCell(4).setCellValue(String.valueOf(company.getInsufficientHouseholds()));
                row.createCell(5).setCellValue(String.valueOf(company.getUnpaidHouseholds()));
                row.createCell(6).setCellValue(String.valueOf(company.getDueAmount()));
                row.createCell(7).setCellValue(String.valueOf(company.getInterestAmount()));
                row.createCell(8).setCellValue(String.valueOf(company.getAddedIncomeQuota()));
                row.createCell(9).setCellValue(String.valueOf(company.getRegionIncomeDistribution()));
                row.createCell(10).setCellValue(String.valueOf(company.getProjectSum()));
                row.createCell(11).setCellValue(String.valueOf(company.getActualAmount()));
                row.createCell(12).setCellValue(String.valueOf(company.getPremium()));
                row.createCell(13).setCellValue(String.valueOf(company.getPremiumReturn()));
                row.createCell(14).setCellValue(String.valueOf(company.getRefundNumber()));
                row.createCell(15).setCellValue(String.valueOf(company.getRefundAmount()));
                row.createCell(16).setCellValue(String.valueOf(company.getFunBal()));

            }

            String titles = DateUtils.getNow() + "";
            String fileName = new String(titles.getBytes("GB2312"), "ISO8859-1");
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;fileName=" + fileName + ".xls");
            OutputStream os = response.getOutputStream();
            //默认Excel名称
            wb.write(os);
            os.flush();
            os.close();
        } catch (IOException e) {
            log.error(IO_EXCRPTION, e.getMessage(), e);
        }
    }


    /**
     * 综合查询导出excel 房屋
     *
     * @param response
     */
    @RequestMapping(value = "/wholeCityComprehensiveQueryHouseCell", method = RequestMethod.POST)
    @ResponseBody
    public void wholeCityComprehensiveQueryHouseCell(@RequestBody StatisticalReportForms s, HttpServletResponse response) {
        try {
            List<StatisticalReportForms> companyList = statisticalReportFormsService.wholeCityComprehensiveQueryHouseCell(s);
            HSSFWorkbook wb = new HSSFWorkbook();
//          工作簿名称
            HSSFSheet sheet = wb.createSheet("房间信息一览表）");
            sheet.setDefaultColumnWidth(9);
//设置某一行列宽的值
            sheet.setColumnWidth(0, 30 * 256);
            sheet.setColumnWidth(1, 14 * 256);
            sheet.setColumnWidth(3, 14 * 256);
            sheet.setColumnWidth(7, 14 * 256);
            sheet.setColumnWidth(8, 14 * 256);
            sheet.setColumnWidth(12, 14 * 256);
            sheet.setColumnWidth(11, 14 * 256);

//          合并单元格
//            CellRangeAddress rowRegion01 = new CellRangeAddress(0, 1, 0, 0);
//            sheet.addMergedRegion(rowRegion01);
//          表头
            int rowNum = 0;
            HSSFRow row = sheet.createRow(rowNum++);
            /** 第三步，设置样式以及字体样式*/
            HSSFCellStyle titleStyle = CreateTitleCellStyle.createTitleCellStyle(wb);
            HSSFCell c00 = row.createCell(0);
            String title = "房间信息一览表";
            c00.setCellValue(title);
            c00.setCellStyle(titleStyle);
            row = sheet.createRow(0);
            //          设置行高;
            row.setHeight((short) (20 * 20));
            row.createCell(0).setCellValue("地址");
            row.createCell(1).setCellValue("账号");
            row.createCell(2).setCellValue("业主姓名");
            row.createCell(3).setCellValue("房屋状态");
            row.createCell(4).setCellValue("面积");
            row.createCell(5).setCellValue("交存额");
            row.createCell(6).setCellValue("利息额");
            row.createCell(7).setCellValue("增值收益分配额");
            row.createCell(8).setCellValue("小区收入分配额");
            row.createCell(9).setCellValue("支用笔数");
            row.createCell(10).setCellValue("支用额");
            row.createCell(11).setCellValue("扣除质保金金额");
            row.createCell(12).setCellValue("质保金返回额");
            row.createCell(13).setCellValue("退款笔数");
            row.createCell(14).setCellValue("退款额");
            row.createCell(15).setCellValue("账户余额");
            //绑定数据
            for (int i = 0; i < companyList.size(); i++) {
                row = sheet.createRow(i + 1);
                StatisticalReportForms company = companyList.get(i);
                row.createCell(0).setCellValue(company.getHouseSite());
                row.createCell(1).setCellValue(String.valueOf(company.getOwnerNo()));
                row.createCell(2).setCellValue(String.valueOf(company.getOwnerName()));
                row.createCell(3).setCellValue(String.valueOf(company.getOwnerState()));
                row.createCell(4).setCellValue(String.valueOf(company.getStruArea()));
                row.createCell(5).setCellValue(String.valueOf(company.getDueAmount()));
                row.createCell(6).setCellValue(String.valueOf(company.getInterestAmount()));
                row.createCell(7).setCellValue(String.valueOf(company.getAddedIncomeQuota()));
                row.createCell(8).setCellValue(String.valueOf(company.getRegionIncomeDistribution()));
                row.createCell(9).setCellValue(String.valueOf(company.getProjectSum()));
                row.createCell(10).setCellValue(String.valueOf(company.getActualAmount()));
                row.createCell(11).setCellValue(String.valueOf(company.getPremium()));
                row.createCell(12).setCellValue(String.valueOf(company.getPremiumReturn()));
                row.createCell(13).setCellValue(String.valueOf(company.getRefundNumber()));
                row.createCell(14).setCellValue(String.valueOf(company.getRefundAmount()));
                row.createCell(15).setCellValue(String.valueOf(company.getFunBal()));

            }

            String titles = DateUtils.getNow() + "";
            String fileName = new String(titles.getBytes("GB2312"), "ISO8859-1");
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;fileName=" + fileName + ".xls");
            OutputStream os = response.getOutputStream();
            //默认Excel名称
            wb.write(os);
            os.flush();
            os.close();
        } catch (IOException e) {
            log.error(IO_EXCRPTION, e.getMessage(), e);
        }
    }

    /**
     * 导出excel  小区欠费信息
     *
     * @param statisticalReportForms 参数的实体
     * @param response
     */
    @RequestMapping(value = "/arrearsInquiriesExport", method = RequestMethod.POST)
    @ResponseBody
    public void arrearsInquiriesExport(@RequestBody StatisticalReportForms statisticalReportForms, HttpServletResponse response) {
        try {

            List<StatisticalReportForms> companyList = statisticalReportFormsService.arrearsInquiriesExport(statisticalReportForms);
            HSSFWorkbook wb = new HSSFWorkbook();
//          工作簿名称
            HSSFSheet sheet = wb.createSheet("小区欠费信息查询");
//1257
            sheet.setDefaultColumnWidth(14);
//设置某一行列宽的值
            sheet.setColumnWidth(9, 18 * 256);
            sheet.setColumnWidth(1, 8 * 256);
//          表头
            HSSFRow row = null;
            row = sheet.createRow(0);
            //          设置行高
            row.setHeight((short) (30 * 20));
            row.createCell(0).setCellValue("小区名称");
            row.createCell(1).setCellValue("总户数");
            row.createCell(2).setCellValue("已维护业主数");
            row.createCell(3).setCellValue("应缴金额（元）");
            row.createCell(4).setCellValue("账户余额（元）");
            row.createCell(5).setCellValue("足额交存数");
            row.createCell(6).setCellValue("足额交存金额（元）");
            row.createCell(7).setCellValue("多缴交户数");
            row.createCell(8).setCellValue("多缴金额（元）");
            row.createCell(9).setCellValue("未足额交存数");
            row.createCell(10).setCellValue("未足额交存金额（元）");
            row.createCell(11).setCellValue("欠费金额（元）");
            //绑定数据
            for (int i = 0; i < companyList.size(); i++) {
                row = sheet.createRow(i + 1);
                StatisticalReportForms company = companyList.get(i);
                row.createCell(0).setCellValue(String.valueOf(company.getRegionName()));
                row.createCell(1).setCellValue(String.valueOf(company.getDepReqSum()));
                row.createCell(2).setCellValue(company.getOwnerStateSum());
                row.createCell(3).setCellValue(String.valueOf(company.getTotalAmount()));
                row.createCell(4).setCellValue(String.valueOf(company.getFunBal()));
                row.createCell(5).setCellValue(String.valueOf(company.getFullHouseholds()));
                row.createCell(6).setCellValue(String.valueOf(company.getFullDepositAmount()));
                row.createCell(7).setCellValue(String.valueOf(company.getFullAllHouseholds()));
                row.createCell(8).setCellValue(String.valueOf(company.getFullAllDepositAmount()));
                row.createCell(9).setCellValue(String.valueOf(company.getInsufficientHouseholds()));
                row.createCell(10).setCellValue(String.valueOf(company.getInsufficientDepositAmount()));
                row.createCell(11).setCellValue(String.valueOf(company.getAmountArrears()));
            }

            String title = DateUtils.getNow() + "";
            String fileName = new String(title.getBytes("GB2312"), "ISO8859-1");
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;fileName=" + fileName + ".xls");
            OutputStream os = response.getOutputStream();
            //默认Excel名称
            wb.write(os);
            os.flush();
            os.close();
        } catch (IOException e) {
            log.error(IO_EXCRPTION, e.getMessage(), e);
        }
    }

    /**
     * 小区欠费业务查询  导出excel
     *
     * @param response
     */
    @RequestMapping(value = "/bankBusinessDetailsExport", method = RequestMethod.POST)
    @ResponseBody
    public void bankBusinessDetailsExport(@RequestBody StatisticalReportForms s, HttpServletResponse response) {
        try {
            List<StatisticalReportForms> companyList = statisticalReportFormsService.selectBankDefiniteCell(s);
            HSSFWorkbook wb = new HSSFWorkbook();
//          工作簿名称
            HSSFSheet sheet = wb.createSheet("业务信息查询");

//          表头
            HSSFRow row = null;
            row = sheet.createRow(0);
            //          设置行高
            row.setHeight((short) (30 * 20));
            row.createCell(0).setCellValue("房屋地址");
            row.createCell(1).setCellValue("业主姓名");
            row.createCell(2).setCellValue("应退金额");
            row.createCell(3).setCellValue("实退金额");
            row.createCell(4).setCellValue("账户余额");
            //绑定数据
            for (int i = 0; i < companyList.size(); i++) {
                row = sheet.createRow(i + 1);
                StatisticalReportForms company = companyList.get(i);
                row.createCell(0).setCellValue(String.valueOf(company.getHouseSite()));
                row.createCell(1).setCellValue(String.valueOf(company.getOwnerName()));
                row.createCell(2).setCellValue(String.valueOf(company.getDueAmount()));
                row.createCell(3).setCellValue(String.valueOf(company.getPayAmount()));
                row.createCell(4).setCellValue(String.valueOf(company.getFullDepositAmount()));
            }
            String title = DateUtils.getNow() + "";
            String fileName = new String(title.getBytes("GB2312"), "ISO8859-1");
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;fileName=" + fileName + ".xls");
            OutputStream os = response.getOutputStream();
            //默认Excel名称
            wb.write(os);
            os.flush();
            os.close();
        } catch (IOException e) {
            log.error(IO_EXCRPTION, e.getMessage(), e);
        }
    }

    /**
     * 小区欠费业务查询  导出excel
     *
     * @param response
     */
    @RequestMapping(value = "/DetailsOfArrearsExport", method = RequestMethod.POST)
    @ResponseBody
    public void detailsOfArrearsExport(@RequestBody StatisticalReportForms s, HttpServletResponse response) {
        try {
            List<StatisticalReportForms> companyList = statisticalReportFormsService.arrearsInquiriesDetailsCell(s);
            HSSFWorkbook wb = new HSSFWorkbook();
//          工作簿名称
            HSSFSheet sheet = wb.createSheet("小区欠费业务查询");

//          表头
            HSSFRow row = null;
            row = sheet.createRow(0);
            sheet.setDefaultColumnWidth(13);
//设置某一行列宽的值
            sheet.setColumnWidth(6, 16 * 256);
            sheet.setColumnWidth(0, 30 * 256);
            //          设置行高
            row.setHeight((short) (30 * 20));
            row.createCell(0).setCellValue("房屋地址");
            row.createCell(1).setCellValue("应缴金额");
            row.createCell(2).setCellValue("账户余额");
            row.createCell(3).setCellValue("缴存累计金额");
            row.createCell(4).setCellValue("退款累计金额");
            row.createCell(5).setCellValue("使用累计金额");
            row.createCell(6).setCellValue("欠费累计金额（元）");
            row.createCell(7).setCellValue("缴存状态");
            row.createCell(8).setCellValue("房屋状态");
            //绑定数据
            for (int i = 0; i < companyList.size(); i++) {
                row = sheet.createRow(i + 1);
                StatisticalReportForms company = companyList.get(i);
                row.createCell(0).setCellValue(String.valueOf(company.getHouseSite()));
                row.createCell(1).setCellValue(String.valueOf(company.getTotalAmount()));
                row.createCell(2).setCellValue(String.valueOf(company.getFunBal()));
                row.createCell(3).setCellValue(String.valueOf(company.getPayAmount()));
                row.createCell(4).setCellValue(String.valueOf(company.getRefundAmount()));
                row.createCell(5).setCellValue(String.valueOf(company.getActualAmount()));
                row.createCell(6).setCellValue(String.valueOf(company.getInsufficientDepositAmount()));
                row.createCell(7).setCellValue(String.valueOf(company.getDueState()));
                row.createCell(8).setCellValue(String.valueOf(company.getState()));
            }
            String title = DateUtils.getNow() + "";
            String fileName = new String(title.getBytes("GB2312"), "ISO8859-1");
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;fileName=" + fileName + ".xls");
            OutputStream os = response.getOutputStream();
            //默认Excel名称
            wb.write(os);
            os.flush();
            os.close();
        } catch (IOException e) {
            log.error(IO_EXCRPTION, e.getMessage(), e);
        }
    }

    /**
     * 银行交易明细查询  导出excel
     *
     * @param response
     */
    @RequestMapping(value = "/bankTransactionDetailsQueryExport", method = RequestMethod.POST)
    @ResponseBody
    public void bankTransactionDetailsQueryExport(@RequestBody  StatisticalReportForms statisticalReportForms, HttpServletResponse response) {
        try {
            List<StatisticalReportForms> companyList = statisticalReportFormsService.selectBankCell(statisticalReportForms);
            HSSFWorkbook wb = new HSSFWorkbook();
//          工作簿名称
            HSSFSheet sheet = wb.createSheet("小区交存业务查询");
//          表头
            HSSFRow row = null;
            row = sheet.createRow(0);
            //          设置行高
            row.setHeight((short) (30 * 20));
            sheet.setDefaultColumnWidth(10);
            sheet.setColumnWidth(4, 20 * 256);
            sheet.setColumnWidth(8, 35 * 256);
            row.createCell(0).setCellValue("承办银行");
            row.createCell(1).setCellValue("记账日期");
            row.createCell(2).setCellValue("业务类型");
            row.createCell(3).setCellValue("交易来源");
            row.createCell(4).setCellValue("业务编号");
            row.createCell(5).setCellValue("收入金额");
            row.createCell(6).setCellValue("支出金额");
            row.createCell(7).setCellValue("结算方式");
            row.createCell(8).setCellValue("摘要");
            row.createCell(9).setCellValue("记账人员");
            //绑定数据
            for (int i = 0; i < companyList.size(); i++) {
                row = sheet.createRow(i + 1);
                StatisticalReportForms company = companyList.get(i);
                row.createCell(0).setCellValue(String.valueOf(company.getBankName()));
                row.createCell(1).setCellValue(company.getReqDate());
                row.createCell(2).setCellValue(company.getBusiType());
                row.createCell(3).setCellValue(String.valueOf(company.getMoneyCome()));
                row.createCell(4).setCellValue(String.valueOf(company.getNO()));
                row.createCell(5).setCellValue(String.valueOf(company.getDueAmount()));
                row.createCell(6).setCellValue(String.valueOf(company.getPayAmount()));
                row.createCell(7).setCellValue(String.valueOf(company.getPayMethod()));
                row.createCell(8).setCellValue(String.valueOf(company.getRemark()));
                row.createCell(9).setCellValue(String.valueOf(company.getName()));

            }

            String title = DateUtils.getNow() + "";
            String fileName = new String(title.getBytes("GB2312"), "ISO8859-1");
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;fileName=" + fileName + ".xls");
            OutputStream os = response.getOutputStream();
            //默认Excel名称
            wb.write(os);
            os.flush();
            os.close();
        } catch (IOException e) {
            log.error(IO_EXCRPTION, e.getMessage(), e);
        }
    }


    /**
     * 小区交存业务查询  导出excel
     *
     * @param statisticalReportForms
     * @param response
     */
    @RequestMapping(value = "/depositBusinessQueryExport", method = RequestMethod.POST)
    @ResponseBody
    public void depositBusinessQueryExport(@RequestBody StatisticalReportForms statisticalReportForms, HttpServletResponse response) {
        try {
            List<StatisticalReportForms> companyList = statisticalReportFormsService.depositBusinessQueryExport(statisticalReportForms);
            HSSFWorkbook wb = new HSSFWorkbook();
//          工作簿名称
            HSSFSheet sheet;
            if (statisticalReportForms.getQueryModes().equals("2")) {
                sheet = wb.createSheet("自然幢交存业务查询");
            } else {
                sheet = wb.createSheet("小区交存业务查询");
            }
            //设置列宽的值
            sheet.setDefaultColumnWidth(12);
            //设置某一行列宽的值

//          表头
            HSSFRow row = null;
            row = sheet.createRow(0);
            //          设置行高
            row.setHeight((short) (30 * 20));

            if (statisticalReportForms.getQueryModes().equals("2")) {
                sheet.setColumnWidth(4, 8 * 256);
                sheet.setColumnWidth(2, 20 * 256);
                row.createCell(0).setCellValue("小区名称");
                row.createCell(1).setCellValue("自然幢名称");
                row.createCell(2).setCellValue("交存申请编号");
                row.createCell(3).setCellValue("交存银行");
                row.createCell(4).setCellValue("总户数");
                row.createCell(5).setCellValue("申请日期");
                row.createCell(6).setCellValue("入账日期");
                row.createCell(7).setCellValue("缴存金额（元）");
                row.createCell(8).setCellValue("资金状态");
            } else {
                sheet.setColumnWidth(3, 8 * 256);
                sheet.setColumnWidth(1, 20 * 256);
                row.createCell(0).setCellValue("小区名称");
                row.createCell(1).setCellValue("交存申请编号");
                row.createCell(2).setCellValue("交存银行");
                row.createCell(3).setCellValue("总户数");
                row.createCell(4).setCellValue("申请日期");
                row.createCell(5).setCellValue("入账日期");
                row.createCell(6).setCellValue("缴存金额（元）");
                row.createCell(7).setCellValue("资金状态");
            }

            //绑定数据
            for (int i = 0; i < companyList.size(); i++) {
                row = sheet.createRow(i + 1);
                StatisticalReportForms company = companyList.get(i);
                if (statisticalReportForms.getQueryModes().equals("1")) {
                    row.createCell(0).setCellValue(String.valueOf(company.getRegionName()));
                    row.createCell(1).setCellValue(company.getNO());
                    row.createCell(2).setCellValue(company.getBankName());
                    row.createCell(3).setCellValue(String.valueOf(company.getDepReqSum()));
                    row.createCell(4).setCellValue(String.valueOf(company.getReqDate()));
                    row.createCell(5).setCellValue(String.valueOf(company.getHfmpDate()));
                    row.createCell(6).setCellValue(String.valueOf(company.getTotalAmount()));
                    if (i < companyList.size() - 1) {
                        row.createCell(7).setCellValue("已入账");
                    } else {
                        row.createCell(7).setCellValue("");
                    }
                } else {
                    row.createCell(0).setCellValue(String.valueOf(company.getRegionName()));
                    row.createCell(1).setCellValue(company.getBuildName());
                    row.createCell(2).setCellValue(company.getNO());
                    row.createCell(3).setCellValue(company.getBankName());
                    row.createCell(4).setCellValue(String.valueOf(company.getDepReqSum()));
                    row.createCell(5).setCellValue(String.valueOf(company.getReqDate()));
                    row.createCell(6).setCellValue(String.valueOf(company.getHfmpDate()));
                    row.createCell(7).setCellValue(String.valueOf(company.getTotalAmount()));
                    if (i < companyList.size() - 1) {
                        row.createCell(8).setCellValue("已入账");
                    } else {
                        row.createCell(8).setCellValue("");
                    }
                }
            }

            String title = DateUtils.getNow() + "";
            String fileName = new String(title.getBytes("GB2312"), "ISO8859-1");
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;fileName=" + fileName + ".xls");
            OutputStream os = response.getOutputStream();
            //默认Excel名称
            wb.write(os);
            os.flush();
            os.close();
        } catch (IOException e) {
            log.error(IO_EXCRPTION, e.getMessage(), e);
        }
    }

    /**
     * 银行交易汇总查询 导出excel
     *
     * @param statisticalReportForms
     * @param response
     */
    @RequestMapping(value = "/bankTransactionSummaryQueryExport", method = RequestMethod.POST)
    @ResponseBody
    public void bankTransactionSummaryQueryExport(@RequestBody StatisticalReportForms statisticalReportForms, HttpServletResponse response) {
        try {

            List<StatisticalReportForms> companyList = statisticalReportFormsService.bankTransactionSummaryQueryExport(statisticalReportForms);
            HSSFWorkbook wb = new HSSFWorkbook();
//          工作簿名称
            HSSFSheet sheet = wb.createSheet("银行交易汇总查询");
//          表头
            HSSFRow row = null;
            row = sheet.createRow(0);
            //          设置行高
            row.setHeight((short) (30 * 20));
            row.createCell(0).setCellValue("承办银行");
            row.createCell(1).setCellValue("专户名称");
            row.createCell(2).setCellValue("专户账号");
            row.createCell(3).setCellValue("交存");
            row.createCell(4).setCellValue("退款");
            row.createCell(5).setCellValue("使用");
            row.createCell(6).setCellValue("期末余额");
            //绑定数据
            for (int i = 0; i < companyList.size(); i++) {
                row = sheet.createRow(i + 1);
                StatisticalReportForms company = companyList.get(i);
                row.createCell(0).setCellValue(String.valueOf(company.getBankName()));
                row.createCell(1).setCellValue(company.getAccName());
                row.createCell(2).setCellValue(company.getBankAcc());
                row.createCell(3).setCellValue(String.valueOf(company.getPayAmount()));
                row.createCell(4).setCellValue(String.valueOf(company.getRefundAmount()));
                row.createCell(5).setCellValue(String.valueOf(company.getActualAmount()));
                row.createCell(6).setCellValue(String.valueOf(company.getInitialPayAmount()));
            }

            String title = DateUtils.getNow() + "";
            String fileName = new String(title.getBytes("GB2312"), "ISO8859-1");
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;fileName=" + fileName + ".xls");
            OutputStream os = response.getOutputStream();
            //默认Excel名称
            wb.write(os);
            os.flush();
            os.close();
        } catch (IOException e) {
            log.error(IO_EXCRPTION, e.getMessage(), e);
        }
    }

}
